package edu.ucsd.irods;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import edu.ucsd.ccdb.generalDataType.DataSectionType;
import edu.ucsd.ccdb.generalDataType.DataValue;
import edu.ucsd.ccdb.generalDataType.MPIDDisplayType;
import edu.ucsd.ccdb.generalDataType.PurposeValue;
import edu.ucsd.ccdb.generalDataType.TagDataValue;
import edu.ucsd.ccdb.tagPopup.dataType.CCDBDescription;
import edu.ucsd.ccdb.tagPopup.dataType.NIFOntologyTerm;

public class SQLDBQuery {
	
	public static Vector <MPIDDisplayType> getAllMicroscopy ( String irodsUsername ) throws Exception {

        Vector<MPIDDisplayType> result = new Vector<MPIDDisplayType>();

        String sql = "select mp.mpid, mp.image_basename from ccdb_permission_view v, " +
        		"microscopy_products mp where v.portal_screenname = ? " +
        		"and v.canwrite = true and v.mpid = mp.mpid ";
        
        Connection c = CeeCeeDBService.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        
        ps.setString(1, irodsUsername);
        
        ResultSet rs = ps.executeQuery();
        while ( rs.next() ) {
        	result.add( new MPIDDisplayType( rs.getInt(1), rs.getString(2) ) );
        }        
        
        CeeCeeDBService.closeConnection(c);
        
        return result;
    }
	
	public static String getHomeDirectory (Integer mpid , String irodsUsername ) throws Exception {
		
		String result = null;
		
		String sql = "select d.prefix || d.postfix as remote_folder from dawg_view d where d.mpid " + 
					"in " +
					"(select v.mpid from ccdb_permission_view v where " +
					"v.portal_screenname = ? and v.canwrite = true and v.mpid= ? )" ; 
		
		Connection c = CeeCeeDBService.getConnection();
		PreparedStatement ps = c.prepareStatement(sql);
		
		ps.setString(1, irodsUsername );
		ps.setInt(2, mpid);
		
		ResultSet rs = ps.executeQuery();
		
		if (rs.next() ){
			result = rs.getString(1);			
		}
		
		CeeCeeDBService.closeConnection(c);
		
		return result;
	}
	
	public static byte[] getImageByteArray ( String path ) throws SQLException {
		String sql = "select i.thumbnail_file from image_info i " +
				" where i.file_path = ? ";
		
		Connection c = DriverManager.getConnection(PostgresConfiguration.URL, 
								PostgresConfiguration.USERNAME, PostgresConfiguration.PASSWORD );
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, path);
		
		ResultSet rs = ps.executeQuery();
		
		byte[] bytes = null;
		
		if (rs.next() ) {
			bytes = rs.getBytes(1);			
		}
		c.close();
		
		return bytes;
	}
	
    public static List<String> getDropDownListOfPurpose () throws Exception {
        List<String> result = new LinkedList<String>();

        String sql = "select data_purpose_id, purpose_name from ccdb_data_purpose " +
                "order by data_purpose_id";

        Connection c = CeeCeeDBService.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        
        ResultSet rs = ps.executeQuery();
        
        while (rs.next() ) {
        	result.add(rs.getString( 2 ));
        }

        CeeCeeDBService.closeConnection(c);        
        return result;
    }
    
    public static List<PurposeValue> getDropDownList () throws Exception {
        List<PurposeValue> result = new LinkedList<PurposeValue>();

        String sql = "select data_section_id, data_purpose_id, purpose_name, table_pk_col from ccdb_data_purpose " +
        			"order by data_purpose_id";

        Connection c = CeeCeeDBService.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        
        ResultSet rs = ps.executeQuery();
        
        while (rs.next() ) {
        	result.add(new PurposeValue(rs.getString(4), rs.getString(3), rs.getInt(2) ));
        }

        CeeCeeDBService.closeConnection(c);        
        return result;
    }
    
    public static List<TagDataValue> getSumaryInfo ( String filePath ) throws Exception {
    	
    	List<TagDataValue> result = new LinkedList<TagDataValue>();
    	
    	String sql = "select cp.purpose_name, cm.file_path, cm.image_set_id, cm.recon_id, cm.seg_id " +
    				"from ccdb_data_mapping cm, ccdb_data_purpose cp " +
    				"where cm.file_path = ? and cm.purpose_id = cp.data_purpose_id";
    	
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, filePath);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	while (rs.next()){
    		
    		Integer data = null;
    		String type = null;
        	if (rs.getObject(3) != null ) {
        		type = "image_set_id";
        		data = rs.getInt(3);
        	}
        	if (rs.getObject(4) != null ) {
        		type = "recon_id";
        		data = rs.getInt(4);
        	}
        	if (rs.getObject(5) != null ) {
        		type = "seg_id";
        		data = rs.getInt(5);
        	}
    		result.add( new TagDataValue( new DataValue(type, data) , rs.getString(1)) ) ;    		
    	}
    	
    	CeeCeeDBService.closeConnection(c);
    	return result;
    }
    
    // check if necessary 
    public static void removeTagCCDB (Integer MPID, IRODSFileString file, DataValue dataValue) throws SQLException {
    	String sql = "delete from ccdb_data_mapping where mpid = ? and file_path = ? ";
    	
    	if (dataValue.getType() == DataSectionType.ImageSetID)
    		sql += " and image_set_id is not null ";
    	if (dataValue.getType() == DataSectionType.ReconID ) 
    		sql += " and recon_id is not null ";
    	if (dataValue.getType() == DataSectionType.SegID ) 
    		sql += " and seg_id is not null ";
    	
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setLong(1, MPID);
    	ps.setString(2, file.getAbsolutePath());
    	ps.executeUpdate();
    	
    	CeeCeeDBService.closeConnection(c);
    }
    
    private static void removeCCDBTagWithPath (String file_path ) throws SQLException {
    	
    	String sql = "delete from ccdb_data_mapping where file_path = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file_path);
    	
    	ps.executeUpdate();
    	
    	CeeCeeDBService.closeConnection(c);
    }
    
    private static int getCCDBDataMapping (String file_path ) throws SQLException {
    	
    	int result = 0;
    	
    	String sql = " select count(*) from ccdb_data_mapping where file_path = ? " ;
    	
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file_path);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()) {
    		result = rs.getInt(1);
    	}
    	
    	CeeCeeDBService.closeConnection(c);
    	
    	return result;
    }
    
    private  static void insertTagCCDB ( Integer MPID, IRODSFileString file, 
    					PurposeValue purposeValue, DataValue dataValue ) throws SQLException, Exception {
    	
    	String sql = "insert into ccdb_data_mapping (ccdb_data_mapping_id, mpid, purpose_id, image_set_id, recon_id, seg_id, file_path, confirmed) " +
    			"values(nextval('general_sequence'), ?, ?, ?, ?, ?, ?, true)  ";
    	
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setInt(1, MPID);
    	ps.setInt(2, purposeValue.getPurposeID() );
    	
    	if ( dataValue.getType() == DataSectionType.ImageSetID ) {
    		
    		// delete entry before adding
    		deleteEntry ( file, DataSectionType.ImageSetID );
    		
    		ps.setInt (3, dataValue.getValue() );
    		ps.setNull(4, Types.INTEGER);
    		ps.setNull(5, Types.INTEGER);
    	}
    	
    	if ( dataValue.getType() == DataSectionType.ReconID ) {
    		
    		//delete entry before adding
    		deleteEntry(file, DataSectionType.ReconID );
    		
    		ps.setNull(3, Types.INTEGER);
    		ps.setInt(4, dataValue.getValue());
    		ps.setNull(5, Types.INTEGER);
    	}
    	
    	if (dataValue.getType() == DataSectionType.SegID ) {
    		
    		// delete entry before adding
    		deleteEntry(file, DataSectionType.SegID);
    		
    		ps.setNull(3, Types.INTEGER);
    		ps.setNull(4, Types.INTEGER);
    		ps.setInt(5, dataValue.getValue());
    	}
    	ps.setString(6, file.getAbsolutePath() );
    	
    	ps.executeUpdate();
    	
    	CeeCeeDBService.closeConnection(c);
    }
    
    public static void manageInsertCCDBTag (Integer MPID, IRODSFileString file, 
			PurposeValue purposeValue, DataValue dataValue) throws Exception {
    	
    	int count = getCCDBDataMapping (file.getAbsolutePath() ) ;
    	
    	if (count != 0 	) {// delete then insert
    		removeCCDBTagWithPath (file.getAbsolutePath());
    	}
    	
    	insertTagCCDB(MPID, file, purposeValue, dataValue);
    }
    
    private static long getTagOnthology ( String tag_name, String onto_url ) throws SQLException {
    	long result = -1;
    	
    	String sql = "select t.tag_id from tag t " +    				    				
    				"where t.tag_name = ? and t.onto_url = ? "; 
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, tag_name);
    	ps.setString(2, onto_url);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()) {
    		result = rs.getLong(1);
    	}
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;
    }
    
    
    /*
     * @return -1 if there is no match, the tag_id if there is a record
     */
    private static long getTaggedMPFileIDOthoFromMPFileID (Integer MPID, String file_path, String screenName,
    														String tag_name, String onto_url ) throws SQLException {
    	
    	long result = -1;
    	
    	String sql = "select tagged_mp_id from tagged_mp_file f " +
    			"inner join ccdb_file cf " +
    			"on f.ccdb_file_id = cf.ccdb_file_id " +
    			"inner join tag t " +
    			"on f.tag_id = t.tag_id " +
    			"where f.mpid = ? and  cf.file_path = ? and f.portal_screenname = ? " +
    				" and t.tag_name = ? and t.onto_url = ? " ;
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setInt(1, MPID);
    	ps.setString(2, file_path);
    	ps.setString(3, screenName);
    	ps.setString(4, tag_name);
    	ps.setString(5, onto_url);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()) {
    		result = rs.getLong(1);
    	}
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;	
    }
    
    public static void deleteTagOthologyMPFile ( Integer MPID, String screenName, String file_path, NIFOntologyTerm data ) throws SQLException {

    	long fileID = getCCDBFileID (file_path); 
    	long tagID = getTagOnthology (data.getLabel(), data.getOntoURL() );
    	
    	// validation
    	if (fileID == -1 || tagID == -1 ) return;
    	
    	String sql = "delete from tagged_mp_file f where " +
    			" tag_id = ? and ccdb_file_id = ? and mpid = ? and portal_screenname = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setLong(1, tagID);
    	ps.setLong(2, fileID);
    	ps.setInt(3, MPID);
    	ps.setString(4, screenName);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);
    }
    
    private static void insertTagOthologyMPFile (long tag_id, Integer MPID, String screenName, long ccdb_file_id) throws SQLException {
    	
    	String sql = "insert into tagged_mp_file (tag_id, mpid,   portal_screenname, ccdb_file_id) values " +
    					"(?, ?, ?, ?) ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setLong(1, tag_id);
    	ps.setInt(2, MPID);    	
    	ps.setString(3, screenName);
    	ps.setLong(4, ccdb_file_id);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);    		
    }
    
    private static long getNextSequence () throws SQLException{
    	long result = -1;
    	
    	String sql = "select nextval ('general_sequence')";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()) 
    		result = rs.getLong(1);
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;
    }
    
    private static long insertTagOthology ( String tag_name,  String file_path, String onthoURL, String screenName) throws SQLException {
    	// get onthology tag_id
    	long result = getNextSequence();
    	int tag_category_id = getTagCategoryID("ontology");
    	
    	String sql = "insert into tag ( tag_id, tag_category_id, tag_name, onto_url, portal_screenname, modified_time ) " +
    			"values (?, ?, ?, ?, ?, ?) ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setLong(1, result );
    	ps.setInt(2, tag_category_id);
    	ps.setString(3, tag_name);
    	ps.setString ( 4, onthoURL );
    	ps.setString ( 5, screenName);
    	ps.setTimestamp(6, new Timestamp( new Date().getTime() ));
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;
    }
    
    private static int getTagCategoryID (String needToSearchCategory) throws SQLException {
    
    	int result = -1 ;
    	
    	String sql = "select tag_category_id from tag_category where tag_category_name = ?";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);    	
    	ps.setString(1, needToSearchCategory);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()) {
    		result = rs.getInt(1);
    	}
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;
    }
    
    private static void updateTagOtho ( long tag_id, String tag_name, String onto_uri ) throws SQLException {
    	
    	String sql = "update tag set tag_name = ?, onto_url = ? " +
    			"where tag_id = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, tag_name);
    	ps.setString(2, onto_uri);
    	ps.setLong(3, tag_id);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);    	
    }
    
    private static long getCCDBFileID (String file_path ) throws SQLException {
    	
    	long result = -1;
    	
    	String sql = "select ccdb_file_id from ccdb_file where file_path = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file_path);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next()){
    		result = rs.getLong(1);
    	}
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;
    }
    
    /*
     * Presume the file_path is missing. The check is on the client to verify
     */
    private static long insertCCDBFile (String file_path ) throws SQLException {
    	
    	long id = getNextSequence (); 
    	
    	String sql = "insert into ccdb_file values (?, ?, '')";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setLong(1, id);
    	ps.setString(2, file_path);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return id;
    }
    
    public static void manageInsertNewTagOnthology (String file_path, String screenName, Integer MPID, 
    									String tag_name, String onto_url) throws SQLException {

    	// check if there is record on the file
    	
    	long ccdbFileID = getCCDBFileID(file_path);
    	
    	if (ccdbFileID == -1 ) // insert new file
    		ccdbFileID = insertCCDBFile(file_path);
    	
    	long taggedMPFileID= getTaggedMPFileIDOthoFromMPFileID (MPID, file_path, screenName, tag_name, onto_url );
    	
    	// if id != -1 then we're done
    	if (taggedMPFileID != -1 ) return ;      	
    	
    	long tag_id = getTagOnthology(tag_name, onto_url);
    	
    	if (tag_id == -1 ) {	// no tag on record
    		tag_id = insertTagOthology ( tag_name, file_path, onto_url, screenName);	
    	}
    	else {
    		updateTagOtho(tag_id, tag_name, onto_url);    		
    	}   
    	
    	// insert more to the tagged_mp_file
    	insertTagOthologyMPFile( tag_id, MPID,  screenName, ccdbFileID);    		
    }
    
    private static void insertCCDBNoteFile (String file_path, String note ) throws SQLException {
    	
    	long id = getNextSequence ();    	
    	String sql = "insert into ccdb_file values (?, ?, ?)";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setLong(1, id);
    	ps.setString(2, file_path);
    	ps.setString(3, note);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);    	
    }

    public static void manageInsertNodeCCDB (String file_path, String note ) throws SQLException {
    	
    	long ccdbFileID = getCCDBFileID(file_path);
    	
    	if (ccdbFileID == -1 ) // insert file + note
    		insertCCDBNoteFile(file_path, note);
    	else // update the note entry
    		updateNoteCCDBFile(file_path, note);
    }
    
    private static void deleteEntry ( IRODSFileString file, DataSectionType type ) throws Exception {
    	
    	// construction the SQL 
    	String sql = "delete from ccdb_data_mapping where file_path = ? ";
    	
    	String typeSQL = null;
    	
    	if (type == DataSectionType.ImageSetID ) 
    		typeSQL = "and image_set_id is not null " ;
    	if (type == DataSectionType.ReconID)
    		typeSQL = "and recon_id is not null " ;
    	if (type == DataSectionType.SegID ) 
    		typeSQL = "and seg_id is not null " ;
    	
    	sql += typeSQL;
    	
    	// construct the
    	Connection c = CeeCeeDBService.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file.getAbsolutePath());
    	ps.executeUpdate();
    	
    	CeeCeeDBService.closeConnection(c);
    }
    
    public static Map<Integer, List<DataValue> > getDataSectionID ( String irodsUsername ) throws Exception {
        Map<Integer, List<DataValue> > result = new HashMap<Integer, List<DataValue>>();

//        String sql = "select m.mpid, r.image_set_id, rec.recon_id, " +
//                "seg.seg_id from microscopy_products m left join image_sets r " +
//                "on (r.mproduct_id = m.mpid) left join image_sets_used iu " +
//                    "on (r.image_set_id = iu.image_set_id) " +
//                    "left join reconstructions rec on " +
//                    "(iu.recon_id = rec.recon_id) " +
//                    "left join member_reconstructions mr " +
//                    "on (rec.recon_id = mr.recon_id) " +
//                    "left join super_reconstructions sr " +
//                    "on (mr.super_recon_id = sr.super_recon_id) " +
//                    "left join segmentation_sets segS " +
//                    "on (sr.super_recon_id = segS.super_recon_id) " +
//                    "left join segmentation seg " +
//                    "on (segS.segmentation_set_id = seg.segmentation_set_id) " +
//                    "where m.mpid in" +
//                    "(select v.mpid from ccdb_permission_view v where " +
//                    "v.portal_screenname = ? and v.canwrite = true) ";

        String sql = "select vcd.mpid, vcd.image_set_id, vcd.recon_id, vcd.seg_id  from ccdb_data_section_view vcd, ccdb_permission_view v " +
        		"where vcd.mpid = v.mpid and v.canwrite= true and v.portal_screenname = ? " ;
        
        Connection c = CeeCeeDBService.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        
        ps.setString(1, irodsUsername );
        
        ResultSet rs = ps.executeQuery();
        
        while (rs.next()){
        	
        	Integer MPID = rs.getInt(1);
        	
        	DataValue dataSection1 = null;
        	DataValue dataSection2 = null;
        	DataValue dataSection3 = null;
        	
        	if ( rs.getObject(2)!= null  ) {
        		dataSection1 = new DataValue ( DataSectionType.ImageSetID, rs.getInt(2) );
        	}        	
        	if (rs.getObject(3)!= null ) {
        		dataSection2 = new DataValue ( DataSectionType.ReconID, rs.getInt(3));
        	}
        	if (rs.getObject(4)!= null ) {
        		dataSection3 = new DataValue ( DataSectionType.SegID , rs.getInt(4) );
        	}
        
        	if (result.containsKey(MPID)){
        		List<DataValue> list = result.get(MPID);
        		//add data
        		
        		if (dataSection1 != null ){
        			list.add(dataSection1);
        		}
        		if (dataSection2 != null ){
        			list.add(dataSection2);
        		}
        		if (dataSection3 != null ){
        			list.add(dataSection3);
        		}
        	}
        	else {// create a new key
                List<DataValue> list = new LinkedList<DataValue>();

                // add new data
                if ( dataSection1 != null )
                    list.add(dataSection1);
                if ( dataSection2 != null )
                    list.add(dataSection2);
                if ( dataSection3 != null )
                    list.add(dataSection3);
                
                result.put(MPID, list );
            }    
        }
        
        return result;
    }
    
    public static CCDBDescription getCCDBIDDescription ( Integer ccdbID ) throws SQLException {
    	
    	CCDBDescription result = null;
    	
    	String sql = "select name, description  from ccdb_data_section_description_view where ccdbid = ?";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setInt(1, ccdbID);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	if (rs.next() ) {    		
    		result = new CCDBDescription(rs.getString(1), rs.getString(2));
    	}
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;    	
    }
    
    public static List<NIFOntologyTerm> getDisplayOthoTag (long MPID, String file_path, String screenName ) throws SQLException {
    	
    	List<NIFOntologyTerm> data = new ArrayList<NIFOntologyTerm>() ;
    	
    	String sql = "select t.tag_name, t.onto_url from tagged_mp_file f " +
    				"join tag t on f.tag_id = t.tag_id " +
    				"join ccdb_file cf on f.ccdb_file_id = cf.ccdb_file_id " +
    				"where f.mpid = ? and cf.file_path = ? and f.portal_screenname = ?";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setLong(1, MPID);
    	ps.setString(2, file_path);
    	ps.setString(3, screenName);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	while (rs.next()) {
    		String label = rs.getString(1);
    		String url = rs.getString(2);
    		
    		data.add( new NIFOntologyTerm(label, url) );
    	}	
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return data; 
    }
    
    public static void updateNoteCCDBFile ( String file_path, String note ) throws SQLException {
    	
    	String sql = "update ccdb_file set note = ? where file_path = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setString(1, note);
    	ps.setString(2, file_path);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);    	
    }
    
    public static String getNoteCCDBFile (String file_path ) throws SQLException {
    	
    	String sql = "select note from ccdb_file where file_path = ?";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ps.setString(1, file_path);
    	
    	ResultSet rs = ps.executeQuery();
    	
    	String result = null;
    	if ( rs.next() )
    		result = rs.getString( 1 );
    			
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;    	
    }
    
    /*
     * Note: This to get the training folder for cytoseg. The cytoSeg is not widely used so this will return
     * 		 a DEMO training folder that is globally available to everyone
     * @return:	list of distinct folder
     */
    public static Set<String> getTrainingFolderCytoSeg () throws SQLException {
    	
    	String sql = "select distinct file_path from image_info ii where ii.mpid in " +
    			"(select mp.mpid from project p, experiment ex, microscopy_products mp " +
    			" where p.project_id = ex.project_id and ex.experiment_id = mp.experiment_experiment_id and p.project_id = 10168 )";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql);
    	ResultSet rs = ps.executeQuery();
    	
    	Set<String> set = new HashSet<String>();
    	
    	while ( rs.next() ) {
    		set.add( getFolderOnPath( rs.getString(1) ) );
		}
    	
    	CCDB_V2_1Service.closeConnection(c);
 	
    	return set;
    }
    
    private static String getFolderOnPath (String data ) {
    	
    	if ( data.endsWith("/"))
    		return data;
    	
    	int index = data.lastIndexOf('/');
    	return data.substring(0, index + 1);
    }
    
    public static void updateFilePath () throws SQLException {
    	
    }
    
    public static void updateChangeNameOnDataMapping (String oldPath, String newPath, boolean isDirectory ) throws SQLException {
    	String sql = "update ccdb_data_mapping set file_path = ? where file_path = ?";
//    	String sql = null;
    	    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setString(1, newPath);
    	ps.setString(2, oldPath);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);
    }
    
    public static void updateChangeImageInfo (String oldPath, String newPath, boolean isDirectory ) throws SQLException {
    	
    	String sql = "update image_info set file_path = ? where file_path = ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setString(1, newPath);
    	ps.setString(2, oldPath);
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);
    }
    
    public static void updateWebURL (String oldPath, String newPath, boolean isDirectory ) throws SQLException {
    	
    	String sql = "update image_info set wib_url = replace (wib_url, ?, ? ) where wib_url IN " +
    				"( select wib_url from image_info where wib_url is not null and wib_url like ? " +
    				")";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	PreparedStatement ps = c.prepareStatement(sql);
    	
    	ps.setString(1, oldPath);
    	ps.setString(2, newPath);
    	ps.setString(3, "%" + oldPath + "%");
    	
    	ps.executeUpdate();
    	
    	CCDB_V2_1Service.closeConnection(c);
    }
    
    public static void manageCutAndPasteNameChange (String oldPath, String newPath, boolean isFolder ) {

    	// TODO Change this to accomondate folder
    	try {
    		updateChangeNameOnDataMapping(oldPath, newPath, isFolder);
    		updateChangeImageInfo(oldPath, newPath, isFolder);
    		updateWebURL(oldPath, newPath, isFolder);
    		
    	}
    	catch (SQLException e) {
    		e.printStackTrace();
    	}
    	
    }
    
    public static List<NIFOntologyTerm> getNIFTerms (String suggestString ) throws SQLException {
    	
    	List<NIFOntologyTerm> result = new ArrayList<NIFOntologyTerm>();
    	
//    	String sql = "select st.preferred_system_name as name, st.system_onto_id as onto_id, st.onto_uri, st.reference_url from system_types st " +
//    			"where st.onto_uri is not null and st.preferred_system_name ilike ? " +
//    			"union " +
//    			"select ct.preferred_cell_name, ct.cell_onto_id, ct.onto_uri, ct.reference_url from cell_types ct " +
//    			"where ct.onto_uri is not null and ct.preferred_cell_name ilike ? " +
//    			"union " +
//    			"select ot.preferred_organ_name, ot.organ_onto_id, ot.onto_uri, ot.reference_url from organ_types ot " +
//    			"where ot.onto_uri is not null and ot.preferred_organ_name ilike ? " +
//    			"union " +
//    			"select at.preferred_anatomical_region_name, at.anatomical_region_onto_id, at.onto_uri, at.reference_url from anatomical_region_types at " +
//    			"where at.onto_uri is not null and at.preferred_anatomical_region_name ilike ? " +
//    			"union " +
//    			"select stt.preferred_structure_name, stt.structure_onto_id, stt.onto_uri, stt.reference_url from structure_types stt " +
//    			"where stt.onto_uri is not null and stt.preferred_structure_name ilike ? " +
//    			"union " +
//    			"select sct.preferred_subcellular_name, sct.subcellular_onto_id, sct.onto_uri, sct.reference_url from subcellular_comp_types sct " +
//    			"where sct.onto_uri is not null and sct.preferred_subcellular_name ilike ? ";
    	
    	String sql = "select * from ccdb_onto_terms_view where name ilike ? ";
    	
    	Connection c = CCDB_V2_1Service.getConnection();
    	
    	PreparedStatement ps = c.prepareStatement(sql); 
    	
    	ps.setString(1, suggestString + "%" );
    	
    	ResultSet rs = ps.executeQuery();
    	
    	while (rs.next()) {
    		result.add( new NIFOntologyTerm( rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4) ) ) ;
    	}
    	
    	CCDB_V2_1Service.closeConnection(c);
    	
    	return result;
    }
}
